package com.jww.dao.provider;

import com.jww.dao.BaseDao;
import com.jww.pojo.Provider;
import com.mysql.jdbc.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class providerDaoImpl implements providerDao {
    // 查询提供公司名称
    @Override
    public List<Provider> queryProvider(Connection conn) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Provider> providersList = new ArrayList<Provider>();
        if (conn != null) {
            String sql = "select * from smbms_provider";
            Object[] params = {};
            rs = BaseDao.execute(conn, ps, rs, sql, params);
            while (rs.next()) {
                Provider provider = new Provider();
                provider.setId(rs.getInt("id"));
                provider.setProCode(rs.getString("proCode"));
                provider.setProName(rs.getString("proName"));
                providersList.add(provider);
            }
            BaseDao.closeResource(null, ps, rs);
        }
        return providersList;
    }

    // 增加供应商
    @Override
    public int add(Connection connection, Provider provider) throws Exception {
        PreparedStatement ps = null;
        int updateRows = 0;
        if (connection != null) {
            String sql = "insert into smbms_provider(proCode, proName,proContact, proPhone, proAddress, proFax, proDesc, createdBy, creationDate)\n"
                    +
                    "values(?,?,?,?,?,?,?,?,?)";

            Object[] params = { provider.getProCode(), provider.getProName(), provider.getProContact(),
                    provider.getProPhone(), provider.getProAddress(), provider.getProFax(),
                    provider.getProDesc(), provider.getCreatedBy(), provider.getCreationDate() };

            updateRows = BaseDao.execute(connection, ps, sql, params);
            BaseDao.closeResource(null, ps, null);
        }
        return updateRows;
    }

    // 通过供应商名称、编码获取供应商列表-模糊查询-providerList
    @Override
    public List<Provider> getProviderList(Connection connection, String proName, String proCode) throws Exception {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        List<Provider> providerList = new ArrayList<Provider>();

        if (connection != null) {
            StringBuffer sql = new StringBuffer();
            sql.append("select * from smbms_provider where 1=1");
            List<Object> list = new ArrayList<Object>();

            if (!StringUtils.isNullOrEmpty(proName)) {
                sql.append(" and proName like ?");
                list.add("%" + proName + "%");
            }

            if (!StringUtils.isNullOrEmpty(proCode)) {
                sql.append(" and proCode like ?");
                list.add("%" + proCode + "%");
            }

            Object[] params = list.toArray();
            System.out.println("sql ----> " + sql.toString());
            rs = BaseDao.execute(connection, pstm, rs, sql.toString(), params);

            while (rs.next()) {
                Provider _provider = new Provider();
                _provider.setId(rs.getInt("id"));
                _provider.setProCode(rs.getString("proCode"));
                _provider.setProName(rs.getString("proName"));
                _provider.setProDesc(rs.getString("proDesc"));
                _provider.setProContact(rs.getString("proContact"));
                _provider.setProPhone(rs.getString("proPhone"));
                _provider.setProAddress(rs.getString("proAddress"));
                _provider.setProFax(rs.getString("proFax"));
                _provider.setCreationDate(rs.getTimestamp("creationDate"));
                providerList.add(_provider);
            }
            BaseDao.closeResource(null, pstm, rs);
        }
        return providerList;
    }

    // 通过proId删除Provider
    @Override
    public int deleteProviderById(Connection connection, int delId) throws Exception {
        PreparedStatement ps = null;
        int updateRows = 0; // 修改了几行数据
        if (connection != null) {
            String sql = "delete from smbms_provider where id = ?";
            Object[] params = { delId };
            updateRows = BaseDao.execute(connection, ps, sql, params);
            BaseDao.closeResource(null, ps, null);
        }
        return updateRows;
    }

    // 通过proId获取Provider
    @Override
    public Provider getProviderById(Connection connection, int id) throws Exception {
        PreparedStatement ps = null;
        ResultSet rs = null;
        Provider provider = null;
        if (connection != null) {
            provider = new Provider();
            String sql = "select * from smbms_provider where id = ?";
            Object[] params = { id };
            rs = BaseDao.execute(connection, ps, rs, sql, params);
            if (rs.next()) {
                provider.setId(rs.getInt("id"));
                provider.setProCode(rs.getString("proCode"));
                provider.setProName(rs.getString("proName"));
                provider.setProDesc(rs.getString("proDesc"));
                provider.setProContact(rs.getString("proContact"));
                provider.setProPhone(rs.getString("proPhone"));
                provider.setProAddress(rs.getString("proAddress"));
                provider.setProFax(rs.getString("proFax"));
                provider.setCreationDate(rs.getTimestamp("creationDate"));
            }
            BaseDao.closeResource(null, ps, rs);
        }
        return provider;
    }

    // 修改供应商信息
    @Override
    public int modify(Connection connection, Provider provider) throws Exception {
        PreparedStatement ps = null;
        int updateRows = 0;
        if (connection != null) {
            String sql = "update smbms_provider set proCode=?,proName=?,proDesc=?," +
                    "proContact=?,proPhone=?,proAddress=?,proFax=?,modifyDate=?,modifyBy=? where id = ?";

            Object[] params = { provider.getProCode(), provider.getProName(), provider.getProDesc(),
                    provider.getProContact(),
                    provider.getProPhone(), provider.getProAddress(), provider.getProFax(), provider.getModifyDate(),
                    provider.getModifyBy(), provider.getId() };
            System.out.println("providerDaoImpl--------------->sql:" + sql);
            updateRows = BaseDao.execute(connection, ps, sql, params);
            BaseDao.closeResource(null, ps, null);
        }
        return updateRows;

    }

    // 根据proCode查询供应商是否存在，用于检查供应商编码唯一性
    @Override
    public Provider selectProCodeExist(Connection connection, String proCode) throws Exception {
        PreparedStatement ps = null;
        ResultSet rs = null;
        Provider provider = null;
        if (connection != null) {
            String sql = "select * from smbms_provider where proCode = ?";
            Object[] params = { proCode };
            rs = BaseDao.execute(connection, ps, rs, sql, params);
            if (rs.next()) {
                provider = new Provider();
                provider.setId(rs.getInt("id"));
                provider.setProCode(rs.getString("proCode"));
                provider.setProName(rs.getString("proName"));
                provider.setProDesc(rs.getString("proDesc"));
                provider.setProContact(rs.getString("proContact"));
                provider.setProPhone(rs.getString("proPhone"));
                provider.setProAddress(rs.getString("proAddress"));
                provider.setProFax(rs.getString("proFax"));
                provider.setCreationDate(rs.getTimestamp("creationDate"));
            }
            BaseDao.closeResource(null, ps, rs);
        }
        return provider;
    }

}
